package com.tang.repository.impl;

import com.mysql.cj.jdbc.JdbcConnection;
import com.tang.entity.Book;
import com.tang.entity.BookCase;
import com.tang.entity.Borrow;
import com.tang.entity.Review;
import com.tang.repository.BorrowRepository;
import com.tang.utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author tang
 * @create 2020-08-18
 */
public class BorrowRepositoryImpl implements BorrowRepository {

    @Override
    public void addborrow(int readId, int bookId, String borrowtime, String returntime) {
        Connection connection = JDBCTools.getConnection();
        String sql = "insert into borrow(bookid,readerid,borrowtime,returntime,state) value (?,?,?,?,0)";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, bookId);
            statement.setInt(2, readId);
            statement.setString(3, borrowtime);
            statement.setString(4, returntime);
            int i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, statement);
        }
    }

    @Override
    public List<Borrow> query(int readerId) {
        Connection connection = JDBCTools.getConnection();
        List<Borrow> list = new ArrayList<>();
        String sql = "SELECT book.`name`,borrow.borrowtime,borrow.returntime,borrow.adminid,borrow.state FROM book,borrow WHERE  borrow.readerid = ? AND book.id = borrow.bookid ";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, readerId);
            resultSet = statement.executeQuery();
         ;
            while (resultSet.next()) {

                list.add(new Borrow(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getInt(4),resultSet.getInt(5)));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,statement,resultSet);
            return list;
        }


    }

    @Override
    public void handle(int id,int adminId, int state) {
        Connection connection = JDBCTools.getConnection();
        String sql = "update borrow set adminid = ?,state= ? where id = ?";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,adminId);
            statement.setInt(2,state);
            statement.setInt(3,id);

            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,statement);
        }


    }

    @Override
    public List<Review> reviewQuery() {
        Connection connection = JDBCTools.getConnection();
        List<Review> list = new ArrayList<>();
        String sql = "SELECT borrow.id,book.`name`,reader.`name`,borrow.borrowtime,borrow.returntime FROM book,borrow,reader WHERE book.id = borrow.bookid AND reader.id = borrow.readerid AND state = 0  ";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Review(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5)));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,statement,resultSet);
            return list;
        }

    }

    @Override
    public List<Review> returnQuery() {
        Connection connection = JDBCTools.getConnection();
        List<Review> list = new ArrayList<>();
        String sql = "SELECT borrow.id,book.`name`,reader.`name`,borrow.borrowtime,borrow.returntime FROM book,borrow,reader WHERE book.id = borrow.bookid AND reader.id = borrow.readerid AND state = 1 ";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Review(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5)));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,statement,resultSet);
            return list;
        }

    }

}

